﻿using System;
using System.Data;
using System.Configuration;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.OleDb;
using System.Text.RegularExpressions;
using System.Text;
using System.Dynamic;
/// <summary>
/// CommonDAL 的摘要说明
/// </summary>

    public static class AccessDAL
    {
        static string conStr = "provider=Microsoft.ACE.OLEDB.12.0;data source=db.accdb";

        public static int ExecuteNonQuery(string text)
        {
            OleDbConnection con = new OleDbConnection(conStr);
            OleDbCommand odc = new OleDbCommand();
            odc.CommandText = text;
            odc.Connection = con;
            con.Open();
            int count = odc.ExecuteNonQuery();
            con.Close();
            con.Dispose();
            return count;

        }
        public static OleDbDataReader ExecuteReader(OleDbConnection con, string text)
        {
            OleDbCommand odc = new OleDbCommand();
            odc.CommandText = text;
            odc.Connection = con;
            con.Open();
            OleDbDataReader dr = odc.ExecuteReader();
            return dr;
        }
        public static object ExecuteScalar(string text)
        {
            OleDbConnection con = new OleDbConnection(conStr);
            OleDbCommand odc = new OleDbCommand();
            odc.CommandText = text;
            odc.Connection = con;
            con.Open();
            object single = odc.ExecuteScalar();
            con.Close();
            con.Dispose();
            return single;
        }
        public static string GetColumnById(int id, string columnout, string table)
        {
            OleDbConnection con = new OleDbConnection(conStr);
            OleDbCommand odc = new OleDbCommand();
            odc.CommandText = "select " + columnout + " from " + table + " where id=" + id;
            odc.Connection = con;
            con.Open();
            string single = odc.ExecuteScalar() + "";
            con.Close();
            con.Dispose();
            return single;
        }
        public static int DeleteById(int id, string table)
        {
            OleDbConnection con = new OleDbConnection(conStr);
            OleDbCommand odc = new OleDbCommand();
            odc.CommandText = "delete from " + table + " where id=" + id;
            odc.Connection = con;
            con.Open();
            int single = odc.ExecuteNonQuery();
            con.Close();
            con.Dispose();
            return single;
        }
        public static string CutString(string stringToSub, int length)
        {
            Regex regex = new Regex("[\u4e00-\u9fa5]+", RegexOptions.Compiled);
            char[] stringChar = stringToSub.ToCharArray();
            StringBuilder sb = new StringBuilder();
            int nLength = 0;
            for (int i = 0; i < stringChar.Length; i++)
            {
                if (regex.IsMatch((stringChar[i]).ToString()))
                {
                    nLength += 2;
                }
                else
                {
                    nLength = nLength + 1;
                }

                if (nLength <= (length * 2))
                {
                    sb.Append(stringChar[i]);
                }
                else
                {
                    break;
                }
            }
            if (sb.ToString() != stringToSub)
            {
                sb.Append("...");
            }
            return sb.ToString();
        }
        //获得一个唯一数
        public static string getUniqueNum()
        {
            string year = DateTime.Now.Year.ToString();
            string month = DateTime.Now.Month.ToString();
            string day = DateTime.Now.Day.ToString();
            string hour = DateTime.Now.Hour.ToString();
            string minute = DateTime.Now.Minute.ToString();
            string second = DateTime.Now.Second.ToString();
            string mim = DateTime.Now.Millisecond.ToString();
            return year + month + day + hour + minute + second + mim;
        }
        public static string getNum()
        {
            string year = DateTime.Now.Year.ToString();
            string month = DateTime.Now.Month.ToString();
            string day = DateTime.Now.Day.ToString();
            return year + month + day;
        }
        public static string AutoNum(string type, string tablename, string fieldname)
        {
            string Num = "";
            OleDbConnection con = new OleDbConnection(conStr);
            con.Open();
            OleDbDataReader sda;
            string sql = "select Right(Max(" + fieldname + ") ,4)  from  " + tablename + "  where  " + fieldname + "  like '" + type + "%' ";
            OleDbCommand cmd = new OleDbCommand(sql, con);
            sda = cmd.ExecuteReader();
            sda.Read();
            if (sda.GetValue(0).ToString() == "")
            {
                Num = type + "0001";
            }
            else
            {
                int num = Convert.ToInt32(sda.GetValue(0)) + 1;
                Num = type + num.ToString("0000");
            }
            sda.Dispose();
            sda.Close();
            con.Close();
            con.Dispose();
            return Num;
        }
        public static List<ServerInfo> FindServerList()
        {

            OleDbConnection con = new OleDbConnection(conStr);
            string sql = "select * from server";
            OleDbDataReader dr = ExecuteReader(con, sql);
            List<ServerInfo> list = new List<ServerInfo>();
            while (dr.Read())
            {
                ServerInfo obj = new ServerInfo();
                obj.ID = DataConvert.ToInt(dr["ID"]);
                obj.Alias = DataConvert.ToString(dr["Alias"]);
                obj.IP = DataConvert.ToString(dr["IP"]);
                obj.UserName = DataConvert.ToString(dr["UserName"]);
                obj.UserPwd = DataConvert.ToString(dr["UserPwd"]);
                list.Add(obj);
            }
            dr.Close();
            con.Close();
            return list;
        }

        public static ServerInfo FindOneByID(int id)
        {

            OleDbConnection con = new OleDbConnection(conStr);
            string sql = "select top 1 * from server where ID="+id;
            OleDbDataReader dr = ExecuteReader(con, sql);
            ServerInfo obj = new ServerInfo();
            if (dr.Read())
            {
                
                obj.ID = DataConvert.ToInt(dr["ID"]);
                obj.Alias = DataConvert.ToString(dr["Alias"]);
                obj.IP = DataConvert.ToString(dr["IP"]);
                obj.UserName = DataConvert.ToString(dr["UserName"]);
                obj.UserPwd = DataConvert.ToString(dr["UserPwd"]);
            }
            dr.Close();
            con.Close();
            return obj;
        }

        public static bool Save(string Alias,string IP,string UserName,string UserPwd)
        {
            int flag = ExecuteNonQuery(string.Format("insert into server(Alias,IP,UserName,UserPwd) values('{0}','{1}','{2}','{3}')", Alias, IP, UserName, UserPwd));
            if (flag == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
        public static bool Update(int ID, string Alias, string IP, string UserName, string UserPwd)
        {
            int flag = ExecuteNonQuery(string.Format("update server set Alias='{0}',IP='{1}',UserName='{2}',UserPwd='{3}' where ID={4}", Alias, IP, UserName, UserPwd , ID));
            if (flag == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }
    }

